// backend/controllers/statsController.js

const db = require('../config/db');
const moment = require('moment'); // 确保引入 moment

exports.getDashboardOverview = async (req, res) => {
    try {
        // 总房间数
        const [totalRoomsResult] = await db.query('SELECT COUNT(*) AS total_rooms FROM Rooms');
        const totalRooms = totalRoomsResult[0].total_rooms;

        // 可用房间数 (空闲)
        const [availableRoomsResult] = await db.query('SELECT COUNT(*) AS available_rooms FROM Rooms WHERE status = "空闲"');
        const availableRooms = availableRoomsResult[0].available_rooms;

        // 入住中房间数 (已入住)
        const [occupiedRoomsResult] = await db.query('SELECT COUNT(*) AS occupied_rooms FROM Rooms WHERE status = "已入住"');
        const occupiedRooms = occupiedRoomsResult[0].occupied_rooms;

        // 今日收入 (来自今日已退房的记录)
        const today = moment().format('YYYY-MM-DD');
        const [todayRevenueResult] = await db.query(
            `SELECT COALESCE(SUM(total_amount), 0) AS today_revenue
             FROM Checkins
             WHERE DATE(checkout_date) = ? AND status = '已退房'`, // 只统计已退房的今日收入
            [today]
        );
        const todayRevenue = parseFloat(todayRevenueResult[0].today_revenue || 0).toFixed(2); // 确保格式化为两位小数

        res.status(200).json({
            totalRooms,
            availableRooms,
            occupiedRooms,
            todayRevenue
        });

    } catch (err) {
        console.error('获取仪表盘概览数据失败:', err);
        res.status(500).json({ error: '获取仪表盘概览数据失败', details: err.message });
    }
};

exports.getRoomStats = async (req, res) => {
    try {
        const { startDate, endDate, room_type } = req.query; // 可以从前端传入日期和房间类型进行过滤
        let query = `
            SELECT
                r.room_type,
                COUNT(c.checkin_id) AS total_checkins, -- 统计该房型总入住次数 (所有状态)
                SUM(CASE WHEN c.status = '已退房' THEN COALESCE(c.total_amount, 0) ELSE 0 END) AS total_revenue_for_type, -- 只统计已退房的收入
                AVG(CASE
                        WHEN c.status = '已退房' AND c.checkin_date IS NOT NULL AND c.checkout_date IS NOT NULL THEN
                            -- 计算入住天数: 如果入住和退房在同一天，算1天。否则，按跨越的自然天数计算 (例如，27号入住，29号退房，算3天)
                            DATEDIFF(c.checkout_date, c.checkin_date) + 1
                        ELSE NULL -- 对于未退房或日期不完整的记录不计算平均天数
                    END) AS avg_stay_days
            FROM Rooms r
                     JOIN Checkins c ON r.room_id = c.room_id
            WHERE 1=1
        `;
        const params = [];

        // 过滤条件应该作用在 Checkins 表上
        if (startDate) {
            query += ' AND c.checkin_date >= ?';
            params.push(startDate);
        }
        if (endDate) {
            // 这里根据你的业务逻辑，是统计结束日期在此范围内的入住，还是入住开始日期在此范围内的。
            // 这里假设是 checkout_date 在此范围内 (对于已退房的记录)
            query += ' AND c.checkout_date <= ?';
            params.push(endDate);
        }
        if (room_type) {
            query += ' AND r.room_type = ?';
            params.push(room_type);
        }

        query += `
            GROUP BY r.room_type
            ORDER BY r.room_type
        `;

        const [rows] = await db.query(query, params);

        // 如果 avg_stay_days 仍然出现 NULL 或非预期值，可以在这里对结果进行后处理
        const processedRows = rows.map(row => ({
            ...row,
            avg_stay_days: row.avg_stay_days !== null ? parseFloat(row.avg_stay_days).toFixed(2) : 'N/A' // 格式化或处理NULL
        }));

        res.status(200).json(processedRows);

    } catch (err) {
        console.error('获取房间销售统计错误:', err);
        res.status(500).json({ error: '获取房间销售统计失败', details: err.message });
    }
};

exports.getConsumptionStats = async (req, res) => {
    try {
        const { startDate, endDate, item_name } = req.query; // 消费时间范围过滤
        let query = `
            SELECT
                mi.item_name,  -- 从 menu_items 表中获取 item_name
                COALESCE(SUM(con.quantity), 0) AS total_quantity,
                COALESCE(SUM(con.amount), 0) AS total_amount
            FROM Consumptions con
            JOIN menu_items mi ON con.item_id = mi.item_id  -- 假设 Consumptions 表通过 item_id 关联 menu_items
            WHERE 1=1
        `;
        const params = [];

        if (startDate) {
            query += ' AND con.consume_time >= ?'; // 按消费时间过滤
            params.push(startDate);
        }
        if (endDate) {
            query += ' AND con.consume_time <= ?'; // 按消费时间过滤
            params.push(endDate);
        }
        if (item_name) {
            query += ' AND mi.item_name LIKE ?';
            params.push(`%${item_name}%`);
        }

        query += `
            GROUP BY mi.item_name
            ORDER BY total_amount DESC
        `;

        const [rows] = await db.query(query, params);
        res.status(200).json(rows);
    } catch (err) {
        console.error('获取消费统计错误:', err);
        res.status(500).json({ error: '获取消费统计失败', details: err.message });
    }
};